export default {
  // 获取案件信息
  getCaseInfo: `select * from tyyw_gg_ajjbxx`,
  // 获取电子卷宗目录
  getDossierCatalog: (id) => {
    return `
select ID,NodeID,case when nodeid=nodeparentid then '' else IFNULL(nodeparentid,'') end NodeParentID,MLXSMC,FileType,wjhz,[order] from (    
${id ? `` : `select * from (select mlbh ID,NodeID,NodeParentID,mlxsmc MLXSMC,'dir' FileType,''wjhz,mlsxh [order] from yx_dzjz_jzml a where (nodeparentid = '' or nodeparentid is null) order by mlsxh) union all`}
select * from(
with RECURSIVE cte as(
select mlbh ID,NodeID,NodeParentID,mlxsmc MLXSMC,'dir' FileType,mlsxh from yx_dzjz_jzml where nodeparentid=${id ? `'${id}'` : `(select nodeid from yx_dzjz_jzml where (nodeparentid = '' or nodeparentid is null) order by mlsxh limit 1)`}
union all 
select  k.mlbh ID,k.NodeID,k.NodeParentID,k.mlxsmc MLXSMC,'dir' FileType,k.mlsxh from yx_dzjz_jzml k inner join cte c on c.nodeid = k.nodeparentid
)select ID,NodeID,NodeParentID,MLXSMC,FileType,''wjhz,mlsxh [order] from cte order by mlsxh)
union all 
select wjxh ID,NodeID,NodeParentID,wjxsmc MLXSMC,'file' FileType,wjhz,wjsxh from yx_dzjz_jzmlwj where ${!process.browser ? `ifnull(pdf,'')!='' and` : ''} nodeparentid in(
with RECURSIVE cte as(    
select NodeID from yx_dzjz_jzml where nodeid=${id ? `'${id}'` : `(select nodeid from yx_dzjz_jzml where (nodeparentid = '' or nodeparentid is null) order by mlsxh limit 1)`}
union all 
select  k.NodeID from yx_dzjz_jzml k inner join cte c on c.nodeid = k.nodeparentid
)select nodeid from cte)
)`
  },

  // 获取电子卷宗文件目录
  getDossierFileCatalog: `
    select wjxh ID,NodeID,NodeParentID,wjxsmc MLXSMC,'file' FileType,wjhz,remark from yx_dzjz_jzmlwj order by wjsxh`,
  // select wjxh ID,mlbh ParentID,wjxsmc MLXSMC,'file' FileType,wjhz,remark from yx_dzjz_jzmlwj order by wjsxh
  // 获取文书卷宗目录
  getDocDossierCatalog: `
    select jzmlh ID,NodeID,case when NodeParentID ='-1' then '' else NodeParentID end NodeParentID, jzmlmc MLXSMC,'dir' FileType from yx_ws_ajjzml`,
  // select jzmlh ID,case when jzfmlh ='-1' then '' else jzfmlh end ParentID, jzmlmc MLXSMC,'dir' FileType from yx_ws_ajjzml
  // 获取文书卷宗文件目录
  getDocDossierFileCatalog: `
    select wsbh ID,NodeID,NodeParentID,wjmc MLXSMC,'file' FileType,wjkzm,remark from yx_ws_ajjzwj ${!process.browser ? `where ifnull(pdf,'')!=''` : ''}  order by wjsx`,
  // select wsbh ID,case when fwsbh ='-1'then jzmlh else fwsbh end ParentID,wjmc MLXSMC,'file' FileType,wjkzm,remark from yx_ws_ajjzwj where LOWER(wjkzm) in('.doc','.docx','.pdf') order by wjsx
  // 获取卷宗文件
  // getDossierFiles: `
  //   select WJBH,ID, IMG,text,WJLX,[order] from (select a.wjxh WJBH, b.ID, b.IMG,b.text,a.wjhz,1 WJLX,b.[order] from yx_dzjz_jzmlwj a left join yx_dzjz_jztpl b on a.wjxh=b.wjxh
  //   union all
  //   select a.wsbh, b.ID, b.IMG,b.text,a.wjkzm,2,[order] from yx_ws_ajjzwj a left join yx_ws_ajjztpl b on a.wsbh=b.wsbh
  //   union all
  //   select a.id,b.ID, b.IMG,b.text,a.wjhz,3,b.[order] from yx_fj_wj a left join yx_fj_tpl b on a.id=b.wjid where b.sfsc='N')`,
  // 获取文件PDF
  getFilePDF (id) {
    return {
      SQL: `select PDF from yx_dzjz_jzmlwj where wjxh=$wjxh
            union 
            select PDF from yx_ws_ajjzwj where wsbh=$wsbh
            union
            select WJL from yx_fj_wj where id=$id`,
      Parameter: {
        $wjxh: id,
        $wsbh: id,
        $id: id
      }
    }
  },
  // 获取外部文件ID
  getExternalFiles (id) {
    return `select id from yx_fj_wj where id in(${id})`
  },
  // 添加外部文件
  addExternalFile: (param) => {
    return {
      sql: `INSERT INTO yx_fj_wj (ID,WJL,WJMC,WJHZ,SCSJ)VALUES($ID,$WJL,$WJMC,$WJHZ,$SCSJ)`,
      param: {
        $ID: param.ID,
        $WJL: param.WJL,
        $WJMC: param.WJMC,
        $WJHZ: param.WJHZ,
        $SCSJ: param.SCSJ
      }
    }
  },
  // 添加外部文件流
  addExternalFileStream: (param) => {
    return {
      SQL: `INSERT INTO yx_fj_tpl (WJID,ID,IMG,TEXT,[ORDER]) VALUES ($WJID,$ID,$IMG,$TEXT,$ORDER)`,
      Parameter: {
        $WJID: param.WJID,
        $ID: param.ID,
        $IMG: param.IMG,
        $TEXT: param.TEXT,
        $ORDER: param.ORDER
      }
    }
  },
  // 修改外部文件
  updateExternalFile: `update yx_fj_wj set wjl=$wjl,img=$img,text=$text where id=$id`,
  // 删除外部文件
  delExternalFile: (param) => {
    return [
      {
        SQL: `delete from yx_fj_wj where id=$ID;`,
        Parameter: {$ID: param}
      },
      {
        SQL: `delete from yx_fj_tpl where WJID=$WJID;`,
        Parameter: {$WJID: param}
      }]
  },
  // 删除外部文件流
  delExternalFileStream: (param) => {
    return {
      SQL: `update yx_fj_tpl set sfsc='Y' where WJID=$WJID and ID=$ID`,
      Parameter: {
        $WJID: param.WJID,
        $ID: param.ID
      }
    }
  },
  // // 根据文件编号获取文件ID
  // getFileIDByCode: (arr) => {
  //   let id = ''
  //   arr.forEach((item, index) => {
  //     id += arr.length - 1 === index ? `'${item}'` : `'${item}',`
  //   })
  //   return {
  //     SQL: `select * from (
  //     select wjxh WJID,ID,[ORDER] from yx_dzjz_jztpl where wjxh in (${id})
  //     union
  //     select wsbh,id,[order] from yx_ws_ajjztpl where wsbh in (${id})
  //     union
  //     select wjid,id,[order] from yx_fj_tpl where wjid in (${id})) order by WJID,[order]`
  //   }
  // },
  // // 根据文件编号获取文件图片
  // getFileByCode: (arr) => {
  //   let id = ''
  //   arr.forEach((item, index) => {
  //     id += arr.length - 1 === index ? `'${item}'` : `'${item}',`
  //   })
  //   return `select * from (
  //       select wjxh WJID,ID,IMG,[ORDER] from yx_dzjz_jztpl where wjxh in (${id})
  //       union
  //       select wsbh,id,img,[order] from yx_ws_ajjztpl where wsbh in (${id})
  //       union
  //       select wjid,id,img,[order] from yx_fj_tpl where wjid in (${id})) order by WJID,[order]`
  // },
  // 根据文件编号获取文件图片
  // getFileByID: (id) => {
  //   return {
  //     sql: `select * from (
  //           select wjxh WJID,ID,IMG,[ORDER] from yx_dzjz_jztpl where ID=$jzid
  //           union
  //           select wsbh,id,img,[order] from yx_ws_ajjztpl where ID in ($wsid)
  //           union
  //           select wjid,id,img,[order] from yx_fj_tpl where ID in ($fjid)) order by WJID,[order]`,
  //     parameter: {
  //       $jzid: id,
  //       $wsid: id,
  //       $fjid: id
  //     }
  //   }
  // },
  // getFilesByID: (arr) => {
  //   let id = ''
  //   arr.forEach((item, index) => {
  //     id += arr.length - 1 === index ? `'${item}'` : `'${item}',`
  //   })
  //   return {
  //     SQL: `select * from (
  //     select wjxh WJID,ID,IMG,[ORDER] from yx_dzjz_jztpl where ID in (${id})
  //     union
  //     select wsbh,id,img,[order] from yx_ws_ajjztpl where ID in (${id})
  //     union
  //     select wjid,id,img,[order] from yx_fj_tpl where ID in (${id})) order by WJID,[order]`
  //   }
  // },
  // clearDelFile: () => {
  //   return [`delete from yx_fj_wj where id not in(select case when wjid is null then '' else wjid end from yx_fj_tpl where sfsc='N')`, `delete from yx_fj_tpl where sfsc='Y'`]
  // },
  getRetrieveData: (type, value) => {
    let sql = ''
    if (Number(type) === 1) {
      sql = `
    select a.mlbh MLBH,b.wjxh WJBH,a.MLXSMC, b.WJXSMC,'电子卷宗' WJLX,b.TEXT,b.WJHZ,b.NodeID from (select MLBH,MLXSMC from yx_dzjz_jzml where mlxsmc like '%${value}%' ) a left join yx_dzjz_jzmlwj b on a.MLBH=b.mlbh 
    union 
    select a.jzmlh,b.wsbh,a.jzmlmc, b.wjmc,'文书卷宗',b.TEXT,b.wjkzm,b.NodeID from (select jzmlh,jzmlmc from yx_ws_ajjzml where jzmlmc like '%${value}%') a left join yx_ws_ajjzwj b on a.jzmlh=b.jzmlh
   `
      // union select '',id,'',wjmc,'附件',TEXT,WJHZ,'' from yx_fj_wj  where wjmc like '%${value}%'
    } else {
      sql = `
      select a.MLBH,b.WJXH WJBH,a.MLXSMC,b.WJXSMC,'电子卷宗' WJLX,b.TEXT,b.WJHZ,b.NodeID from yx_dzjz_jzml a inner join yx_dzjz_jzmlwj b on a.mlbh = b.mlbh where b.text like '%${value}%'
      union
      select a.jzmlh,b.wsbh,a.jzmlmc,b.wjmc,'文书卷宗' WJLX,b.TEXT,b.wjkzm,b.NodeID from yx_ws_ajjzml a inner join yx_ws_ajjzwj b on a.jzmlh = b.jzmlh where b.text like '%${value}%'`
      // union select '',id,'',wjmc,'附件',TEXT,WJHZ,'' from yx_fj_wj where text like '%${value}%'
    }
    return {SQL: sql, Parameter: {}}
  },
  getDossierNodeIDs: `select nodeid from yx_dzjz_jzmlwj`

  /**********************************************************************/

//   DelDossiers (ID) {
//     return [
//       {
//         SQL: `delete from yx_dzjz_jztpl where wjxh in(
// select wjxh from yx_dzjz_jzmlwj where nodeparentid in(
// with RECURSIVE cte as(
// select NodeID from yx_dzjz_jzml where nodeparentid=$nodeparentid
// union all
// select k.NodeID from yx_dzjz_jzml k inner join cte c on c.nodeid = k.nodeparentid
// )select NodeID from cte
// union
// select NodeID from yx_dzjz_jzml where nodeid=$nodeid));`,
//         Parameter: {$nodeparentid: ID, $nodeid: ID}
//       },
//       {
//         SQL: `delete from yx_dzjz_jzmlwj where nodeparentid in(
// with RECURSIVE cte as(
// select NodeID from yx_dzjz_jzml where nodeparentid=$nodeparentid
// union all
// select k.NodeID from yx_dzjz_jzml k inner join cte c on c.nodeid = k.nodeparentid
// )select NodeID from cte
// union
// select NodeID from yx_dzjz_jzml where nodeid=$nodeid);`,
//         Parameter: {$nodeparentid: ID, $nodeid: ID}
//       },
//       {
//         SQL: `delete from yx_dzjz_jzml where NodeID in(
// with RECURSIVE cte as(
// select NodeID from yx_dzjz_jzml where nodeparentid=$nodeparentid
// union all
// select k.NodeID from yx_dzjz_jzml k inner join cte c on c.nodeid = k.nodeparentid
// )select NodeID from cte
// union
// select NodeID from yx_dzjz_jzml where nodeid=$nodeid);`,
//         Parameter: {$nodeparentid: ID, $nodeid: ID}
//       }
//     ]
//   },

  // /**
  //  * 删除电子卷宗文件
  //  * @param ID
  //  * @returns {*[]}
  //  * @constructor
  //  */
  // DelDossierFile (ID) {
  //   return [
  //     {
  //       SQL: `delete from yx_dzjz_jzmlwj where WJXH=$WJXH;`,
  //       Parameter: {$WJXH: ID}
  //     },
  //     {
  //       SQL: `delete from yx_dzjz_jztpl where WJXH=$WJXH;`,
  //       Parameter: {$WJXH: ID}
  //     }]
  // },
  // /**
  //  *  删除电子卷宗目录
  //  * @param ID
  //  * @returns {*[]}
  //  * @constructor
  //  */
  // DelDossierDir (ID) {
  //   return [
  //     {
  //       SQL: `delete from yx_dzjz_jzml where MLBH=$MLBH;`,
  //       Parameter: {$MLBH: ID}
  //     }]
  // },
  /**
   * 删除文书卷宗文件
   * @param ID
   * @returns {*[]}
   * @constructor
   */
  // DelDocDossierFile (ID) {
  //   return [
  //     {
  //       SQL: `delete from yx_ws_ajjzwj where WSBH=$WSBH;`,
  //       Parameter: {$WSBH: ID}
  //     },
  //     {
  //       SQL: `delete from yx_ws_ajjztpl where WSBH=$WSBH;`,
  //       Parameter: {$WSBH: ID}
  //     }]
  // },
  /**
   *  删除文书卷宗目录
   * @param ID
   * @returns {*[]}
   * @constructor
   */
  // DelDocDossierDir (ID) {
  //   return [
  //     {
  //       SQL: `delete from yx_ws_ajjzml where JZMLH=$JZMLH;`,
  //       Parameter: {$JZMLH: ID}
  //     }]
  // }

}
